package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import persistence.jdbc.JDBC_Config;


public class DatabaseInitScript  {

    /** sqlServer parameters */
    private Connection sqlConnection;

    public DatabaseInitScript(){
    	JDBC_Config.SetConfig();
    	try {
			this.sqlConnection = DriverManager.getConnection(JDBC_Config.URL+JDBC_Config.DBNAME,
					JDBC_Config.USERNAME, JDBC_Config.PASSWORD);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return;
		}
    }


    public static void main(String[] args) {

    	System.out.println("initializing database...");
		DatabaseInitScript creator = new DatabaseInitScript();
		JDBC_Config.SetConfig();
		creator.dropForumDB();
		creator.createForumDB();
		creator.createUserTable();
		creator.createItemTable();
		creator.createPostTable();
		creator.createTopicTable();
		creator.createThreadTable();
		creator.createStat1Table();
		creator.createStat2Table();
		creator.createStat3Table();
		creator.putItemValues();
		creator.putUserValues();
		System.out.println("finsihed initializing database!");
    }

	private void createStat3Table() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE stat3 (hour INT NOT NULL, value INT NOT NULL);";
			s.executeUpdate(sql);
			for (int i=0; i<24; i++) {
				sql = "INSERT INTO stat3 VALUES ("+i+", 0);";
				s.executeUpdate(sql);
			}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return;
			}
			System.out.println("created table 'stat3'.");		
	}


	private void createStat2Table() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE stat2 (hour INT NOT NULL, value INT NOT NULL);";
			s.executeUpdate(sql);
			for (int i=0; i<24; i++) {
				sql = "INSERT INTO stat2 VALUES ("+i+", 0);";
				s.executeUpdate(sql);
			}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return;
			}
			System.out.println("created table 'stat2'.");	
	}


	private void createStat1Table() {
		// TODO Auto-generated method stub
		
	}


	private void putUserValues() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "INSERT INTO users VALUES ('admin','wsep092@gmail.com',2009,3,'1644308851');";
			s.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("could not put values in users");
			return;
		}
	}

	private void dropForumDB() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "DROP DATABASE "+JDBC_Config.DBNAME+";";
			s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("could not delete database "+JDBC_Config.DBNAME+", maybe not exists!");
				System.out.println(e.getMessage());
				return;
			}
			System.out.println("deleted old database "+JDBC_Config.DBNAME+".");
	}

	private void createForumDB() {
		try {
		Statement s = this.sqlConnection.createStatement();
		String sql = "CREATE DATABASE "+JDBC_Config.DBNAME+"";
		s.executeUpdate(sql);
	//	if (JDBC_Config.STATE.equals("home")) {
			sql = "USE "+JDBC_Config.DBNAME+"";
			s.executeUpdate(sql);	
	//	}
		} catch (SQLException e) {
			System.out.println("could not create database "+JDBC_Config.DBNAME+", maybe already exists!");
			return;
		}
		System.out.println("created new database "+JDBC_Config.DBNAME+".");
	}

	private void createItemTable() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE items (id INT NOT NULL, name VARCHAR(100) NOT NULL, type INT NOT NULL, PRIMARY KEY (id), INDEX(id)) ENGINE = INNODB;";
			s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("could not create table 'items', maybe already exists!");
				return;
			}
			System.out.println("created table 'items'.");
	}


	private void createThreadTable() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE threads (id INT NOT NULL, postId INT NOT NULL, PRIMARY KEY (id,postId), INDEX(id), FOREIGN KEY (id) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (postId) REFERENCES posts (id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = INNODB;";
			s.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("could not create table 'threads', maybe already exists!");
			return;
		}
		System.out.println("created table 'therads'.");
	}


	private void createTopicTable() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE topics (id INT NOT NULL, subItem INT NOT NULL, PRIMARY KEY (id,subItem), INDEX(id), FOREIGN KEY (id) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (subItem) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = INNODB;";
			s.executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println("could not create table 'topics', maybe already exists!");
			return;
		}
		System.out.println("created table 'topics'.");
	}

	private void putItemValues() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "INSERT INTO items VALUES (1, 'root', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO items VALUES (100, 'Year 1', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (1, 100);";
			s.executeUpdate(sql);
			sql = "INSERT INTO items VALUES (101, 'Year 2', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (1, 101);";
			s.executeUpdate(sql);
			sql = "INSERT INTO items VALUES (102, 'Year 3', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (1, 102);";
			s.executeUpdate(sql);
			sql = "INSERT INTO items VALUES (103, 'Year 4', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (1, 103);";
			s.executeUpdate(sql);
			sql = "INSERT INTO items VALUES (10200, 'Workshop on SE Project', 0);";
			s.executeUpdate(sql);
			sql = "INSERT INTO topics VALUES (102, 10200);";
			s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("could not put values in items");
				return;
			}
			System.out.println("values inserted to items.");
	}

	private void createUserTable() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE users (username VARCHAR(20) NOT NULL, email VARCHAR(50),salt INT, rank INT, password VARCHAR(20), PRIMARY KEY (username)) ENGINE=INNODB;";
			s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("could not create table 'users', maybe already exists!");
				return;
			}
			System.out.println("created table 'users'.");
	}

	private void createPostTable() {
		try {
			Statement s = this.sqlConnection.createStatement();
			String sql = "CREATE TABLE posts (id INT NOT NULL, FOREIGN KEY (id) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE, content TEXT, owner VARCHAR(20) NOT NULL, FOREIGN KEY (owner) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE, date DATETIME, PRIMARY KEY (id)) ENGINE=INNODB;";
			s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("could not create table 'posts', maybe already exists!");
				return;
			}
			System.out.println("created table 'posts'.");
	}

}
